using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace WCFServer
{
    public class DbManager
    {
        //private static string connectionString = ConfigurationManager.AppSettings["WcfServerDB"].ToString();
        private const string INSERTMESSAGEQUERY = "INSERT INTO Mensajes (Titulo ,[Procesado]) VALUES (@Title,@Processed);SELECT SCOPE_IDENTITY();";
        private const string INSERTATTRIBUTEQUERY = "INSERT INTO Atributos (MensajeId ,Tipo ,Nombre ,Valor) VALUES (@MessageId,@AttrType,@AttrName,@AttrValue)";
        private const string SELECTLASTID = "SELECT SCOPE_IDENTITY()";
        private const string GETMESSAGEATTRIBUTES = "SELECT * FROM ATRIBUTOS WHERE MENSAJEID = @MsgId";




        public static List<WcfMessage> getMessages(string sqlQuery, List<WcfParameter> parameters, string connectionString)
        {
            List<WcfMessage> msgList = new List<WcfMessage>();
            DataSet dsResult = new DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(sqlQuery, connection);
                if (parameters != null)
                {
                    foreach (WcfParameter par in parameters)
                    {
                        cmd.Parameters.AddWithValue(par.ParameterName, par.ParameterValue);
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                try
                {
                    connection.Open();
                    da.Fill(dsResult);
                    foreach (DataRow row in dsResult.Tables[0].Rows)
                    {
                        msgList.Add(new WcfMessage()
                        {
                            MensajeId = Int32.Parse(row["MensajeId"].ToString()),
                            Titulo = row["Titulo"].ToString(),
                            Procesado = row["Procesado"].ToString() == "1"
                        });
                    }
                  
                }
                catch (Exception ex)
                {

                }
            }
            return msgList;
        }

        public static List<WcfMsgAttribute> getMessageAttributes(int msgID, string connectionString)
        {
            List<WcfMsgAttribute> attributeList = new List<WcfMsgAttribute>();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(GETMESSAGEATTRIBUTES, connection);
                cmd.Parameters.AddWithValue("@MsgId", msgID);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataSet dsResult = new DataSet();
                try
                {
                    connection.Open();
                    da.Fill(dsResult);
                    foreach (DataRow row in dsResult.Tables[0].Rows)
                    {
                        attributeList.Add(new WcfMsgAttribute()
                        {
                            Id = Int32.Parse(row["MensajeId"].ToString()),
                            AttrName = row["Nombre"].ToString(),
                            AttrType = row["Tipo"].ToString(),
                            AttrValue = row["Valor"].ToString(),
                            MessageId = msgID
                            
                        });
                    }
                    
                }
                catch (Exception ex)
                {


                }
            }
            return attributeList;
        }

        public static void markAsRead(string sqlQuery, List<WcfParameter> parameters, string connectionString)
        {
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConn.Open();
                    SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn);
                    if (parameters != null)
                    {
                        foreach (WcfParameter par in parameters)
                        {
                            sqlCmd.Parameters.AddWithValue(par.ParameterName, par.ParameterValue);
                        }
                    }
                    sqlCmd.ExecuteNonQuery();

                }
                catch (Exception ex)
                {

                }

            }
        }

        public static void insertNewMessage(WcfMessage msg, string connectionString)
        {
            SqlTransaction trans = null;
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {

                try
                {
                    sqlConn.Open();
                    

                    trans = sqlConn.BeginTransaction();
             
                    //Tell the SqlCommand what query to execute and what SqlConnection to use.
                    SqlCommand sqlCmd = new SqlCommand(INSERTMESSAGEQUERY, sqlConn,trans);
                    sqlCmd.Parameters.AddWithValue("@Title", msg.Titulo);
                    sqlCmd.Parameters.AddWithValue("Processed", Convert.ToByte(msg.Procesado));

                    //Guardo el mensaje
                    var MessageID = sqlCmd.ExecuteScalar();

                    //Guardo los atributos
                    foreach (WcfMsgAttribute attrib in msg.AttributeList)
                    {
                         SqlCommand cmdAttrib = new SqlCommand(INSERTATTRIBUTEQUERY, sqlConn,trans);
                         cmdAttrib.Parameters.AddWithValue("@MessageId", MessageID);
                         cmdAttrib.Parameters.AddWithValue("@AttrType", attrib.AttrType);
                         cmdAttrib.Parameters.AddWithValue("@AttrName", attrib.AttrName);
                         cmdAttrib.Parameters.AddWithValue("@AttrValue", attrib.AttrValue);
                         cmdAttrib.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                    catch (Exception ex)
                    {
                        //There is a problem connecting to the instance of the SQL Server.
                        //For example, the connection string might be wrong,
                        //or the SQL Server might not be available to you.
                    }

                
            }
        }
    }
}